use WebSecurity;
GO
-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_CATALOG = N'WebSecurity' 
	 AND SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'Membership_Search' 
)
   DROP PROCEDURE dbo.Membership_Search
GO
create procedure Membership_Search
    @PageSize int ,
    @PageNumber int, 
    @SortColumn nVarChar (100) ,
    @SortType nVarChar (4),
    @TotalRows int = null OUTPUT, 
@ConfirmationToken NVarChar(128) = N'%' , 
@Beg_LastPasswordFailureDate DateTime = '01/01/1800' , 
@End_LastPasswordFailureDate DateTime = '12/31/2032' , 
@Beg_PasswordFailuresSinceLastSuccess Int = -1 , 
@End_PasswordFailuresSinceLastSuccess Int = 999999 , 
@Password NVarChar(128) = N'%' , 
@Beg_PasswordChangedDate DateTime = '01/01/1800' , 
@End_PasswordChangedDate DateTime = '12/31/2032' , 
@PasswordSalt NVarChar(128) = N'%' , 
@PasswordVerificationToken NVarChar(128) = N'%' , 
@Beg_PasswordVerificationTokenExpirationDate DateTime = '01/01/1800' , 
@End_PasswordVerificationTokenExpirationDate DateTime = '12/31/2032' , 
@EmailAccount NVarChar(255) = N'%' , 
@PasswordAnswer NVarChar(250) = N'%' , 
	@RowsAffected int = null OUTPUT 
--*************************************************************
--Created By: trooks
--Name: .dbo_Membership_Search
--Table: Membership
--Database: WebSecurity
--Created Date: 01/21/2013 00:00:00
--*************************************************************

as
	Begin Try
        Declare @StartIndex int = (@PageNumber - 1) * @PageSize + 1
        Declare @EndingIndex int = (@PageSize - 1) * @PageNumber + @PageNumber 
        Set @TotalRows = (Select Count(*) from dbo.Membership)
        IF @SortType = 'asc'
				BEGIN
					WITH tempMembership  AS
					(
                        SELECT	ROW_NUMBER() OVER (ORDER BY @SortColumn  asc ) AS RowNumber,
		MembershipID, 
		ConfirmationToken, 
		IsConfirmed, 
		LastPasswordFailureDate, 
		PasswordFailuresSinceLastSuccess, 
		Password, 
		PasswordChangedDate, 
		PasswordSalt, 
		PasswordVerificationToken, 
		PasswordVerificationTokenExpirationDate, 
		TS, 
		CreatedBy, 
		CreatedDt, 
		ModifiedBy, 
		ModifiedDt, 
		EmailAccount, 
		PasswordAnswer, 
		LastLoginDate, 
		LastLockoutDate, 
		UserId, 
		PasswordQuestionID 
        
		From [dbo].Membership 
Where ConfirmationToken Like '%' + @ConfirmationToken + '%'  AND LastPasswordFailureDate Between @Beg_LastPasswordFailureDate AND @End_LastPasswordFailureDate  AND PasswordFailuresSinceLastSuccess Between @Beg_PasswordFailuresSinceLastSuccess AND @End_PasswordFailuresSinceLastSuccess  AND Password Like '%' + @Password + '%'  AND PasswordChangedDate Between @Beg_PasswordChangedDate AND @End_PasswordChangedDate  AND PasswordSalt Like '%' + @PasswordSalt + '%'  AND PasswordVerificationToken Like '%' + @PasswordVerificationToken + '%'  AND PasswordVerificationTokenExpirationDate Between @Beg_PasswordVerificationTokenExpirationDate AND @End_PasswordVerificationTokenExpirationDate  AND EmailAccount Like '%' + @EmailAccount + '%'  AND PasswordAnswer Like '%' + @PasswordAnswer + '%'  
		)
                    SELECT	RowNumber, 
		MembershipID, 
		ConfirmationToken, 
		IsConfirmed, 
		LastPasswordFailureDate, 
		PasswordFailuresSinceLastSuccess, 
		Password, 
		PasswordChangedDate, 
		PasswordSalt, 
		PasswordVerificationToken, 
		PasswordVerificationTokenExpirationDate, 
		TS, 
		CreatedBy, 
		CreatedDt, 
		ModifiedBy, 
		ModifiedDt, 
		EmailAccount, 
		PasswordAnswer, 
		LastLoginDate, 
		LastLockoutDate, 
		UserId, 
		PasswordQuestionID 
                    FROM	tempMembership
					WHERE	RowNumber BETWEEN @StartIndex AND @EndingIndex
					ORDER BY RowNumber ASC;
                    
		        END
			ELSE
                BEGIN
					WITH tempMembership  AS
					(
                        SELECT	ROW_NUMBER() OVER (ORDER BY @SortColumn  desc ) AS RowNumber,
		MembershipID, 
		ConfirmationToken, 
		IsConfirmed, 
		LastPasswordFailureDate, 
		PasswordFailuresSinceLastSuccess, 
		Password, 
		PasswordChangedDate, 
		PasswordSalt, 
		PasswordVerificationToken, 
		PasswordVerificationTokenExpirationDate, 
		TS, 
		CreatedBy, 
		CreatedDt, 
		ModifiedBy, 
		ModifiedDt, 
		EmailAccount, 
		PasswordAnswer, 
		LastLoginDate, 
		LastLockoutDate, 
		UserId, 
		PasswordQuestionID 
        
		From [dbo].Membership 
Where ConfirmationToken Like '%' + @ConfirmationToken + '%'  AND LastPasswordFailureDate Between @Beg_LastPasswordFailureDate AND @End_LastPasswordFailureDate  AND PasswordFailuresSinceLastSuccess Between @Beg_PasswordFailuresSinceLastSuccess AND @End_PasswordFailuresSinceLastSuccess  AND Password Like '%' + @Password + '%'  AND PasswordChangedDate Between @Beg_PasswordChangedDate AND @End_PasswordChangedDate  AND PasswordSalt Like '%' + @PasswordSalt + '%'  AND PasswordVerificationToken Like '%' + @PasswordVerificationToken + '%'  AND PasswordVerificationTokenExpirationDate Between @Beg_PasswordVerificationTokenExpirationDate AND @End_PasswordVerificationTokenExpirationDate  AND EmailAccount Like '%' + @EmailAccount + '%'  AND PasswordAnswer Like '%' + @PasswordAnswer + '%'  
		)
                    SELECT	RowNumber, 
		MembershipID, 
		ConfirmationToken, 
		IsConfirmed, 
		LastPasswordFailureDate, 
		PasswordFailuresSinceLastSuccess, 
		Password, 
		PasswordChangedDate, 
		PasswordSalt, 
		PasswordVerificationToken, 
		PasswordVerificationTokenExpirationDate, 
		TS, 
		CreatedBy, 
		CreatedDt, 
		ModifiedBy, 
		ModifiedDt, 
		EmailAccount, 
		PasswordAnswer, 
		LastLoginDate, 
		LastLockoutDate, 
		UserId, 
		PasswordQuestionID 
                    FROM	tempMembership
					WHERE	RowNumber BETWEEN @StartIndex AND @EndingIndex
					ORDER BY RowNumber ASC;
                    
		        END
		Set @RowsAffected = @@ROWCOUNT;
		return 0;	
	END TRY
	BEGIN CATCH
		Set @RowsAffected = 0;
		EXEC dbo.uspLogError;
		return -1;
	END CATCH
	
GO
--GRANT EXECUTE ON [WebSecurity].[dbo].[Membership_Search] to PORTAL_WebSecurity